﻿namespace Jhong.Data.Core.Common
{
    using Jhong.Data.Core.Infrastructure;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Text;

    public abstract class DBContext : IDisposable, IDBMethod, IDBTranslation, IDBEntity
    {
        protected DBContext(IDbConnection connection)
        {
            this.Connection = connection;
        }

        private IDbConnection _connection;

        protected IDbConnection Connection
        {
            get
            {
                if (this._connection.State != ConnectionState.Open) this._connection.Open();
                return this._connection;
            }
            private set
            {
                this._connection = value;
            }
        }

        protected List<Tuple<string, DbParameter[]>> _execList = new List<Tuple<string, DbParameter[]>>();

        public virtual DBContext S(string sqlstr)
        {
            return this.S(sqlstr, null);
        }

        public virtual DBContext S(string sqlstr, DbParameter[] parameters)
        {
            this._execList.Add(new Tuple<string, DbParameter[]>(sqlstr, parameters));
            return this;
        }

        public virtual void Dispose()
        {
            if (null != this.Translation)
            {
                this.Rollback();
                this.Translation = null;
            }
            if (this.Connection.State == ConnectionState.Open) this.Connection.Close();
        }

        public abstract int[] Execute();


        public abstract IDbCommand GetDBCommand(string sqlStr, DbParameter[] parameters);
        

        public abstract DataTable[] QueryToDataTables();

        protected void ClearExexList()
        {
            this._execList.Clear();
        }

        protected IDbTransaction Translation { get; private set; }

        public void Commit()
        {
            this.Translation.Commit();
            this.Translation.Dispose();
            this.Translation = null;
        }

        public void Rollback()
        {
            this.Translation.Rollback();
            this.Translation.Dispose();
            this.Translation = null;
        }

        public void BeginTranslation()
        {
            this.Translation = this.Connection.BeginTransaction();
        }

        public void BeginTranslation(IsolationLevel level)
        {
            this.Translation = this.Connection.BeginTransaction(level);
        }

        public T[] QueryToEntitys<T>() where T : new()
        {
            this.CheckExecNumber();
            var data = this.QueryToDataTables()[0];
            var entityArray = new T[data.Rows.Count];
            for (int i = 0; i < data.Rows.Count; i++)
            {
                entityArray[i] = EntityConverter<T>.Convert(data.Rows[i]);
            }
            return entityArray;
        }

        private void CheckExecNumber()
        {
            var count = this._execList.Count;
            if (count > 1 || count < 0) throw new Exception("查询任务数只能有且只有一个");
        }

        public T QueryFirstOrDefault<T>() where T : new()
        {
            this.CheckExecNumber();
            var dt = this.QueryToDataTables()[0];
            if (dt.Rows.Count == 0) return default(T);
            if (dt.Columns.Count == 1) return (T)Convert.ChangeType(dt.Rows[0][0], typeof(T));
            return EntityConverter<T>.Convert(dt.Rows[0]);
        }

        public int InsertEntity<T>(T model)
        {
            var key = new StringBuilder();
            var value = new StringBuilder();
            var dbParameterList = new List<DbParameter>();
            foreach (var currColumn in EntityContext<T>.Columns)
            {
                if (currColumn.Key && currColumn.Autoincrement) continue;
                key.Append(string.Format("{0},", currColumn.ColumnName));
                value.Append(string.Format("@{0},", currColumn.ColumnName));
                dbParameterList.Add(this.GetDBParameter(currColumn.ColumnName, currColumn.GetterDelegate(model)));
            }
            if (key.Length > 0) key.Remove(key.Length - 1, 1);
            if (value.Length > 0) value.Remove(value.Length - 1, 1);
            return this.CommnEntityExecute(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", EntityContext<T>.TableName, key.ToString(), value.ToString()), dbParameterList.ToArray());
        }

        public int UpdateEntity<T>(T model)
        {
            var where = string.Empty;
            var valueStr = new StringBuilder();
            var columnCount = EntityContext<T>.Columns.Count();
            var dbparameterList = new List<DbParameter>();
            foreach (var currColumn in EntityContext<T>.Columns)
            {
                dbparameterList.Add(this.GetDBParameter(currColumn.ColumnName, currColumn.GetterDelegate(model)));
                if (currColumn.Key)
                {
                    where = string.Format("{0}=@{0}", currColumn.ColumnName);
                    continue;
                }
                valueStr.Append(string.Format("{0}=@{0},", currColumn.ColumnName));
            }
            if (string.IsNullOrWhiteSpace(where)) throw new Exception("实体模型未设置主键");
            if (valueStr.Length > 0) valueStr.Remove(valueStr.Length - 1, 1);
            return this.CommnEntityExecute(string.Format("UPDATE {0} SET {1} WHERE {2}", EntityContext<T>.TableName, valueStr.ToString(), where), dbparameterList.ToArray());
        }

        public int DeleteEntity<T>(T model)
        {
            var key = (from c in EntityContext<T>.Columns where c.Key select c).FirstOrDefault();
            if (null == key) throw new Exception("实体未设置主键");
            return this.CommnEntityExecute(string.Format("DELETE FROM {0} WHERE {1}",
                EntityContext<T>.TableName,
                string.Format("{0}=@{0}", key.ColumnName)
                ),
                new DbParameter[] { this.GetDBParameter(key.ColumnName, key.GetterDelegate(model)) }
                );
        }

        public int Delete<T>(string keyValue)
        {
            var key = (from c in EntityContext<T>.Columns where c.Key select c).FirstOrDefault();
            if (null == key) throw new Exception("实体未设置主键");
            return this.CommnEntityExecute(string.Format("DELETE FROM {0} WHERE {1}",
                EntityContext<T>.TableName,
                string.Format("{0}=@{0}", key.ColumnName)
                ),
                new DbParameter[] { this.GetDBParameter(key.ColumnName, keyValue) }
                );
        }

        private int CommnEntityExecute(string sqlstr, DbParameter[] parameters)
        {
            this.ClearExexList();
            this.S(sqlstr, parameters);
            return this.Execute()[0];
        }

        public virtual DBContext T(string sqlstr, params object[] args)
        {
            var dbParameters = this.BuildDBParameters(args);
            return this.S(sqlstr, dbParameters);
        }

        public virtual DBContext T(string sqlstr)
        {
            return this.S(sqlstr);
        }

        protected virtual DbParameter GetDBParameter(int index, object value)
        {
            return this.GetDBParameter(index.ToString(), value);
        }

        protected virtual DbParameter GetDBParameter(string parameterName, object value)
        {
            throw new NotImplementedException();
        }

        private DbParameter[] BuildDBParameters(params object[] args)
        {
            var count = args.Count();
            var dbParameters = new DbParameter[count];
            for (int i = 0; i < count; i++) dbParameters[i] = this.GetDBParameter(i, args[i]);
            return dbParameters;
        }
    }
}